﻿/*
 * Copyright © 2025−2026 WEI.ZHOU (Willis) (Chengdu). All rights reserved.
 *
 * THIS SOURCE CODE IS PROPRIETARY AND CONFIDENTIAL.
 * It is the exclusive property of WEI.ZHOU (Willis) and may not be
 * used, copied, distributed, or disclosed outside of WEI.ZHOU (Willis)
 * without express written permission.
 *
 * This code is provided to WEI.ZHOU (Willis) under a limited, 
 * non-transferable internal license, and is for use only with the author's 
 * approved purposes. No external license is granted or implied.
 *
 * Any unauthorized use, reproduction, or distribution is strictly prohibited
 * and may result in severe civil and criminal penalties.
 */
 
using Aore.Core.Factorys.DbConnection;
using Aore.Utility.Dapper;
using Aore.Utility.Extensions;
using Dapper;
using Dapper.Contrib.Extensions;
using System.Data;


// ReSharper disable RedundantCatchClause
// ReSharper disable MethodOverloadWithOptionalParameter
// ReSharper disable RedundantTypeArgumentsOfMethod
// ReSharper disable ConditionIsAlwaysTrueOrFalseAccordingToNullableAPIContract
// ReSharper disable PublicConstructorInAbstractClass
// ReSharper disable ConvertToPrimaryConstructor
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable ConvertToUsingDeclaration
// ReSharper disable SuggestVarOrType_SimpleTypes
// ReSharper disable SuggestVarOrType_Elsewhere
// ReSharper disable InconsistentNaming
// ReSharper disable SuggestVarOrType_BuiltInTypes
#pragma warning disable CS8603 // 可能返回 null 引用。

namespace Aore.Core.Repository.Dapper;

public sealed class DbRepository<TEntity> : IDbRepository<TEntity> where TEntity : class, new()
{
  /// <summary>
  /// DB Connection
  /// </summary>
  private readonly IDbConnection _connection;
  
  public DbRepository(IDbConnection connection)
  {
    _connection = connection;
  }
  
  public DbRepository(IDbConnectionFactory connectionFactory)
  {
    _connection = connectionFactory.CreateConnection();
  }
  
  #region Connection基础操作
    /// <summary>
    /// 打开数据库连接,如果关闭或者连接中断,则打开连接
    /// </summary>
    public void OpenConnection(IDbConnection conn)
    {
      if (conn == null)
      {
        throw new Exception("Connection cannot be empty, please initialize the connection object");
      }

      if (conn.State == ConnectionState.Closed)
      {
        conn.Open();
      }
      else if (conn.State == ConnectionState.Broken)
      {
        conn.Close();
        conn.Open();
      }
    }

    /// <summary>
    /// 打开数据库连接,如果关闭或者连接中断,则打开连接
    /// </summary>
    public void OpenConnection()
    {
      if (_connection == null)
      {
        throw new Exception("Connection cannot be empty, please initialize the connection object");
      }

      if (_connection.State == ConnectionState.Closed)
      {
        _connection.Open();
      }
      else if (_connection.State == ConnectionState.Broken)
      {
        _connection.Close();
        _connection.Open();
      }
    }

    /// <summary>
    /// 以指定的 System.Data.IsolationLevel 值开始一个数据库事务。
    /// </summary>
    /// <param name="il">指定连接的事务锁定行为</param>
    /// <returns>表示新事务的对象</returns>
    public IDbTransaction BeginTransaction(IsolationLevel il)
    {
      OpenConnection(this._connection);
      return this._connection.BeginTransaction(il);
    }

    /// <summary>
    /// 开始一个数据库事务
    /// </summary>
    /// <returns></returns>
    public IDbTransaction BeginTransaction()
    {
      OpenConnection(this._connection);
      return this._connection.BeginTransaction();
    }

    /// <summary>
    /// 为打开的 Connection 对象更改当前数据库
    /// </summary>
    /// <param name="dataBaseName">要代替当前数据库进行使用的数据库的名称</param>
    public void ChangeDataBase(string dataBaseName)
    {
      this._connection.ChangeDatabase(dataBaseName);
    }

    /// <summary>
    /// 创建并返回一个与该连接相关联的 Command 对象
    /// </summary>
    /// <returns>一个与该连接相关联的 Command 对象</returns>
    public IDbCommand CreateCommand()
    {
      return this._connection.CreateCommand();
    }
    #endregion
    
    /// <summary>
    /// Dapper 执行SQL返回DataTable类型
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public DataTable QueryDataTable(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);
            return dt;
          }
        }
        
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryDataTableIn 执行SQL返回DataTable类型,该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public DataTable QueryDataTableIn(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);
            return dt;
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Dapper 执行SQL返回DataTable类型
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public DataRow QueryDataTableFirstRow(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);
            if (dt == null || dt.Rows.Count == 0)
              throw new Exception("No data found, please check the data source or SQL！");
            return dt.FirstRow();
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryDataTableIn 执行SQL返回DataTable类型,该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public DataRow QueryDataTableInFirstRow(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);
            if (dt == null || dt.Rows.Count == 0)
              throw new Exception("No data found, please check the data source or SQL！");
            return dt.FirstRow();
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryString 执行SQL返回string类型的單個數據
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public string? QueryString(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true,
      int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);

            if (dt.Rows.Count > 0)
              return Convert.ToString(dt.Rows[0][0]);
            else
              return null;
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryStringIn 执行SQL返回String类型的單個數據,该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public string? QueryStringIn(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          sql = DapperCommon.ParameterReplace(conn, sql);
          ListExtensions.ParamIsListEmpty(ref param);
          using (IDataReader d = conn.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
          {
            DataTable dt = new DataTable();
            dt.Load(d);
            
            if (dt.Rows.Count > 0)
              return Convert.ToString(dt.Rows[0][0]);
            else
              return null;
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 单条SQL，多次执行（每次执行传递数据不同），该方法执行SQL采用事务控制
    /// <para>
    /// 默认调用MesCon连接字符串
    /// </para>
    /// </summary>
    /// <param name="sqlParamDir"></param>
    /// <exception cref="Exception"></exception>
    public void DapperExecuteBatch(Dictionary<string, List<object>> sqlParamDir)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          using (var trans = conn.BeginTransaction())
          {
            try
            {
              foreach (var sqlParam in sqlParamDir)
              {
                string sql = sqlParam.Key;
                sql = DapperCommon.ParameterReplace(conn, sql);
                //判断SQL结尾是否有分号【;】，存在分号就将其裁切掉
                if (sql.EndsWith(";"))
                {
                  sql = sql.Substring(0, sql.Length - 1);
                }

                if (sqlParam.Value.Count > 0)
                {
                  sqlParam.Value.ForEach(param => { conn.Execute(sql, param, trans); });
                }
                else
                {
                  conn.Execute(sql, trans);
                }
              }

              trans.Commit();
            }
            catch
            {
              trans.Rollback();
              throw;
            }
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 单条SQL，多次执行（每次执行传递数据不同），该方法执行SQL采用事务控制
    /// <para>
    /// 默认调用MesCon连接字符串
    /// </para>
    /// <para>
    /// 该方法执行出现错误，可以通过Out返回错误信息
    /// </para>
    /// </summary>
    /// <param name="sqlParamDir"></param>
    /// <param name="errorMsg"></param>
    /// <exception cref="Exception"></exception>
    public void DapperExecuteBatch(Dictionary<string, List<object>> sqlParamDir, out string errorMsg)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          using (var trans = conn.BeginTransaction())
          {
            try
            {
              foreach (var sqlParam in sqlParamDir)
              {
                string sql = sqlParam.Key;
                sql = DapperCommon.ParameterReplace(conn, sql);
                //判断SQL结尾是否有分号【;】，存在分号就将其裁切掉
                if (sql.EndsWith(";"))
                {
                  sql = sql.Substring(0, sql.Length - 1);
                }

                if (sqlParam.Value.Count > 0)
                {
                  sqlParam.Value.ForEach(param => { conn.Execute(sql, param, trans); });
                }
                else
                {
                  conn.Execute(sql, trans);
                }
              }

              trans.Commit();

              errorMsg = string.Empty;
            }
            catch (Exception ex)
            {
              trans.Rollback();
              errorMsg = ex.Message + ex.StackTrace;
              throw;
            }
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合批量（循环）执行
    /// <para>
    /// return [Row] 表示执行成功返回受影响行数；[-1] 表示执行异常（回退异常）；
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public int BatchExecutionForeach(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 0;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              foreach (var item in list)
              {
                string sqlOriginal = item.Trim();
                // 确保SQL不以【;】结尾
                if (sqlOriginal.EndsWith(";") || sqlOriginal.EndsWith("；"))
                {
                  sqlOriginal = sqlOriginal.Substring(0, sqlOriginal.Length - 1);
                }

                int ResultRow = conn.Execute(sqlOriginal, transaction: transaction);
                if (ResultRow >= 0)
                {
                  ExecutionRow += ResultRow;
                }
              }

              transaction.Commit();
              return ExecutionRow;
            }
            catch
            {
              transaction.Rollback();
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合批量（循环）执行
    /// <para>
    /// return [Row] 表示执行成功返回受影响行数；[-1] 表示执行异常（回退异常）；
    /// </para>
    /// <para>
    /// 该方法执行出现错误，可以通过Out返回错误信息
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <param name="errorMsg">异常内容</param>
    /// <returns>执行状态</returns>
    public int BatchExecutionForeach(List<string> list, out string errorMsg)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 0;
          string sql = string.Empty;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              foreach (var item in list)
              {
                string sqlOriginal = item.Trim();
                // 确保SQL不以【;】结尾
                if (sqlOriginal.EndsWith(";") || sqlOriginal.EndsWith("；"))
                {
                  sqlOriginal = sqlOriginal.Substring(0, sqlOriginal.Length - 1);
                }

                sql = sqlOriginal;
                int ResultRow = conn.Execute(sqlOriginal, transaction: transaction);
                if (ResultRow >= 0)
                {
                  ExecutionRow += ResultRow;
                }
              }

              transaction.Commit();

              errorMsg = string.Empty;
              return ExecutionRow;
            }
            catch (Exception ex)
            {
              transaction.Rollback();

              errorMsg = ex.Message + $"exception sql[{sql}]";
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合拼接为字符串采用BeginEnd执行，SQL集合中的SQL必须以[;]结尾
    /// <para>
    /// return [1] 表示执行成功（提交事务）；[-1] 表示执行异常（回退异常），出现异常可能为SQL集合中部分SQL语句错误，或者SQL结尾未加[;]
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public int BatchExecutionBeginEnd(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 1;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              string sql = $@"begin
";
              foreach (string item in list)
              {
                string tempSql = item;
                if (!tempSql.EndsWith(";"))
                {
                  tempSql += ";";
                }

                sql += tempSql + "\r\n";
              }

              sql += "end;";

              conn.Execute(sql, transaction: transaction);
              transaction.Commit();
              return ExecutionRow;
            }
            catch
            {
              transaction.Rollback();
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合拼接为字符串采用BeginEnd执行，SQL集合中的SQL必须以[;]结尾
    /// <para>
    /// return [1] 表示执行成功（提交事务）；[-1] 表示执行异常（回退异常），出现异常可能为SQL集合中部分SQL语句错误，或者SQL结尾未加[;]
    /// </para>
    /// <para>
    /// 该方法执行出现错误，可以通过Out返回错误信息
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <param name="errorMsg">异常错误信息</param>
    /// <returns>执行状态</returns>
    public int BatchExecutionBeginEnd(List<string> list, out string errorMsg)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 1;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              string sql = $@"begin
";
              foreach (string item in list)
              {
                string tempSql = item;
                if (!tempSql.EndsWith(";"))
                {
                  tempSql += ";";
                }

                sql += tempSql + "\r\n";
              }

              sql += "end;";

              conn.Execute(sql, transaction: transaction);
              transaction.Commit();

              errorMsg = string.Empty;
              return ExecutionRow;
            }
            catch (Exception ex)
            {
              transaction.Rollback();

              errorMsg = ex.Message + ex.StackTrace;
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)SQL集合批量（循环）执行
    /// <para>
    /// return [Row] 表示执行成功返回受影响行数；执行出现异常将转抛异常
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行完成受影响的行数</returns>
    public int BatchExecutionForeachNts(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 0;
          try
          {
            foreach (var item in list)
            {
              string sqlOriginal = item.Trim();
              // 确保SQL不以【;】结尾
              if (sqlOriginal.EndsWith(";") || sqlOriginal.EndsWith("；"))
              {
                sqlOriginal = sqlOriginal.Substring(0, sqlOriginal.Length - 1);
              }

              int ResultRow = conn.Execute(sqlOriginal);
              if (ResultRow >= 0)
              {
                ExecutionRow += ResultRow;
              }
            }

            return ExecutionRow;
          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message, ex);
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)SQL集合拼接为字符串采用BeginEnd执行，SQL集合中的SQL必须以[;]结尾
    /// <para>
    /// return [1] 表示执行成功；执行异常将抛出异常信息
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public int BatchExecutionBeginEndNts(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionRow = 1;
          try
          {
            string sql = $@"begin
";
            foreach (string item in list)
            {
              string tempSql = item;
              if (!tempSql.EndsWith(";"))
              {
                tempSql += ";";
              }

              sql += tempSql + "\r\n";
            }

            sql += "end;";
            conn.Execute(sql);
            return ExecutionRow;
          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message, ex);
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合批量（循环）执行
    /// <para>
    /// return [Row] 表示执行成功返回受影响行数；[-1] 表示执行异常（回退异常）；
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public async Task<int> BatchExecutionForeachAsync(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          int ExecutionRow = 0;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              foreach (var item in list)
              {
                string sqlOriginal = item.Trim();
                // 确保SQL不以【;】结尾
                if (sqlOriginal.EndsWith(";") || sqlOriginal.EndsWith("；"))
                {
                  sqlOriginal = sqlOriginal.Substring(0, sqlOriginal.Length - 1);
                }

                int ResultRow = await conn.ExecuteAsync(sqlOriginal, transaction: transaction);
                ExecutionRow += ResultRow;
              }

              transaction.Commit();
              return ExecutionRow;
            }
            catch
            {
              transaction.Rollback();
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// SQL集合拼接为字符串采用BeginEnd执行，SQL集合中的SQL必须以[;]结尾
    /// <para>
    /// return [1] 表示执行成功（提交事务）；[-1] 表示执行异常（回退异常），出现异常可能为SQL集合中部分SQL语句错误，或者SQL结尾未加[;]
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public async Task<int> BatchExecutionBeginEndAsync(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          int ExecutionRow = 1;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              string sql = $@"begin
";
              foreach (string item in list)
              {
                string tempSql = item;
                if (!tempSql.EndsWith(";"))
                {
                  tempSql += ";";
                }

                sql += tempSql + "\r\n";
              }

              sql += "end;";

              await conn.ExecuteAsync(sql, transaction: transaction);
              transaction.Commit();
              return ExecutionRow;
            }
            catch
            {
              transaction.Rollback();
              ExecutionRow = -1;
            }
          }

          return ExecutionRow;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)SQL集合批量（循环）执行
    /// <para>
    /// return [Row] 表示执行成功返回受影响行数；执行出现异常将转抛异常
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行完成受影响的行数</returns>
    public async Task<int> BatchExecutionForeachAsyncNts(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          int ExecutionRow = 0;
          try
          {
            foreach (var item in list)
            {
              string sqlOriginal = item.Trim();
              // 确保SQL不以【;】结尾
              if (sqlOriginal.EndsWith(";") || sqlOriginal.EndsWith("；"))
              {
                sqlOriginal = sqlOriginal.Substring(0, sqlOriginal.Length - 1);
              }

              int ResultRow = await conn.ExecuteAsync(sqlOriginal);
              ExecutionRow += ResultRow;
            }

            return ExecutionRow;
          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message, ex);
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)SQL集合拼接为字符串采用BeginEnd执行，SQL集合中的SQL必须以[;]结尾
    /// <para>
    /// return [1] 表示执行成功；执行异常将抛出异常信息
    /// </para>
    /// </summary>
    /// <param name="list">SQL集合</param>
    /// <returns>执行状态</returns>
    public async Task<int> BatchExecutionBeginEndAsyncNts(List<string> list)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          int ExecutionRow = 1;
          try
          {
            string sql = $@"begin
";
            foreach (string item in list)
            {
              string tempSql = item;
              if (!tempSql.EndsWith(";"))
              {
                tempSql += ";";
              }

              sql += tempSql + "\r\n";
            }

            sql += "end;";
            await conn.ExecuteAsync(sql);
            return ExecutionRow;
          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message, ex);
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="ProcName">存储过程名称</param>
    /// <param name="dynamicParameters">存储过程参数对象（包含input，output）</param>
    /// <returns>执行存储过程状态</returns>
    public int ExecuteProcedure(string ProcName, DynamicParameters dynamicParameters)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionStatus = 1;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              conn.Execute(ProcName, dynamicParameters, commandType: CommandType.StoredProcedure);
              transaction.Commit();
              return ExecutionStatus;
            }
            catch
            {
              transaction.Rollback();
              ExecutionStatus = -1;
            }
          }

          return ExecutionStatus;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 执行存储过程
    /// <para>
    /// 该方法执行出现错误，可以通过Out返回错误信息
    /// </para>
    /// </summary>
    /// <param name="ProcName">存储过程名称</param>
    /// <param name="dynamicParameters">存储过程参数对象（包含input，output）</param>
    /// <param name="errorMsg"></param>
    /// <returns>执行存储过程状态</returns>
    public int ExecuteProcedure(string ProcName, DynamicParameters dynamicParameters, out string errorMsg)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          int ExecutionStatus = 1;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              conn.Execute(ProcName, dynamicParameters, commandType: CommandType.StoredProcedure);
              transaction.Commit();

              errorMsg = string.Empty;
              return ExecutionStatus;
            }
            catch (Exception ex)
            {
              transaction.Rollback();

              errorMsg = ex.Message;
              ExecutionStatus = -1;
            }
          }

          return ExecutionStatus;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// ExecuteSqlListAsync,异步批量执行Sql List
    /// </summary>
    /// <param name="sqlList">SQLS 对象集合</param>
    /// <returns></returns>
    public async Task ExecuteSqlListAsync(IEnumerable<string> sqlList)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              foreach (var sql in sqlList)
              {
                await conn.ExecuteAsync(sql, transaction: transaction);
              }

              transaction.Commit();
            }
            catch
            {
              transaction.Rollback();
              throw;
            }
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// ExecuteSqlListAsyncResultRows,异步批量执行Sql List
    /// </summary>
    /// <param name="sqlList">SQLS对象集合</param>
    /// <returns>执行结果</returns>
    public async Task<int> ExecuteSqlListAsyncResultRows(IEnumerable<string> sqlList)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          int ResultRows = 0;
          using (var transaction = conn.BeginTransaction())
          {
            try
            {
              foreach (var sql in sqlList)
              {
                int status = await conn.ExecuteAsync(sql, transaction: transaction);
                ResultRows += status;
              }

              transaction.Commit();
              return ResultRows;
            }
            catch
            {
              transaction.Rollback();
              throw;
            }
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)ExecuteSqlListAsync,异步批量执行Sql List，取消事务管理
    /// </summary>
    /// <param name="sqlList">SQLS对象集合</param>
    /// <returns>执行异常会抛出对应错误异常</returns>
    public async Task ExecuteSqlListAsyncNts(IEnumerable<string> sqlList)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          foreach (var sql in sqlList)
          {
            await conn.ExecuteAsync(sql);
          }
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// (无事务)ExecuteSqlListAsyncResultRowsNTS,异步批量执行Sql List，取消事务管理，执行成功返回受影响的行数
    /// </summary>
    /// <param name="sqlList">SQLS对象集合</param>
    /// <returns>执行成功返回受影响的行数，执行异常会抛出对应错误异常</returns>
    public async Task<int> ExecuteSqlListAsyncResultRowsNts(IEnumerable<string> sqlList)
    {
      try
      {
        using (var conn = _connection)
        {
          int resultRows = 0;
          OpenConnection(conn);
          foreach (var sql in sqlList)
          {
            int status = await conn.ExecuteAsync(sql);
            resultRows += status;
          }

          return resultRows;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 根据主键获取实体数据
    /// </summary>
    /// <param name="key">主键值</param>
    /// <returns></returns>
    public TEntity Get(object key)
    {
      return Get<TEntity>(key);
    }

    /// <summary>
    ///  根据主键获取实体数据
    /// </summary>
    /// <param name="key"></param>
    /// <param name="traction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public TEntity Get(object key, IDbTransaction traction = null, int? commandTimeout = null)
    {
      return Get<TEntity>(key, traction, commandTimeout);
    }

    /// <summary>
    /// 根据主键获取实体数据
    /// </summary>
    /// <param name="key">主键值</param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public async Task<TEntity> GetAsync(object key, IDbTransaction traction = null, int? commandTimeout = null)
    {
      return await GetAsync<TEntity>(key);
    }

    /// <summary>
    /// 根据主键获取实体数据
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="key">主键值</param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public T Get<T>(object key, IDbTransaction traction = null, int? commandTimeout = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Get<T>(key, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 根据主键获取实体数据
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="key">主键值</param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public async Task<T> GetAsync<T>(object key, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.GetAsync<T>(key, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 获取所有的实体数据
    /// </summary>
    /// <returns></returns>
    public IEnumerable<TEntity> GetAll()
    {
      return GetAll<TEntity>();
    }

    /// <summary>
    /// 获取所有的实体数据
    /// </summary>
    /// <returns></returns>
    public IEnumerable<TEntity> GetAll(IDbTransaction traction = null, int? commandTimeout = null)
    {
      return GetAll<TEntity>(traction, commandTimeout);
    }

    /// <summary>
    /// Async 获取所有的实体数据
    /// </summary>
    /// <returns></returns>
    public async Task<IEnumerable<TEntity>> GetAllAsync(IDbTransaction traction = null, int? commandTimeout = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.GetAllAsync<TEntity>(traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 获取所有的实体数据
    /// </summary>
    /// <returns></returns>
    public IEnumerable<T> GetAll<T>(IDbTransaction traction = null, int? commandTimeout = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.GetAll<T>(traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 获取所有的实体数据
    /// </summary>
    /// <returns></returns>
    public async Task<IEnumerable<T>> GetAllAsync<T>(IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.GetAllAsync<T>(traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 插入实体数据
    /// </summary>
    /// <param name="entityToInsert"></param>
    /// <returns></returns>
    public bool Insert(TEntity entityToInsert)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Insert<TEntity>(entityToInsert) > 0;
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 插入实体数据
    /// </summary>
    /// <param name="entityToInsert"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>    
    /// <returns></returns>
    public bool Insert(TEntity entityToInsert, IDbTransaction traction = null, int? commandTimeout = null)
    {
      return Insert<TEntity>(entityToInsert, traction, commandTimeout) > 0;
    }

    /// <summary>
    /// Async 插入实体数据
    /// </summary>
    /// <param name="entityToInsert"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>    
    /// <param name="sqlAdapter"></param>
    /// <returns></returns>
    public async Task<bool> InsertAsync(TEntity entityToInsert, IDbTransaction traction = null,
      int? commandTimeout = null, ISqlAdapter sqlAdapter = null)
    {
      return await InsertAsync<TEntity>(entityToInsert, traction, commandTimeout) > 0;
    }

    /// <summary>
    /// 插入实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToInsert"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public long Insert<T>(T entityToInsert, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Insert<T>(entityToInsert, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 插入实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToInsert"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="sqlAdapter"></param>
    /// <returns></returns>
    public async Task<long> InsertAsync<T>(T entityToInsert, IDbTransaction traction = null, int? commandTimeout = null,
      ISqlAdapter sqlAdapter = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.InsertAsync<T>(entityToInsert, traction, commandTimeout, sqlAdapter);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 更新实体数据
    /// </summary>
    /// <param name="entityToUpdate"></param>
    /// <returns></returns>
    public bool Update(TEntity entityToUpdate)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Update<TEntity>(entityToUpdate);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    ///  更新实体数据
    /// </summary>
    /// <param name="entityToUpdate"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public bool Update(TEntity entityToUpdate, IDbTransaction traction = null, int? commandTimeout = null)
    {
      return Update<TEntity>(entityToUpdate, traction, commandTimeout);
    }

    /// <summary>
    /// Async 更新实体数据
    /// </summary>
    /// <param name="entityToUpdate"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public async Task<bool> UpdateAsync(TEntity entityToUpdate, IDbTransaction traction = null,
      int? commandTimeout = null)
    {
      return await UpdateAsync<TEntity>(entityToUpdate);
    }

    /// <summary>
    /// 更新实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToUpdate"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public bool Update<T>(T entityToUpdate, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Update<T>(entityToUpdate, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 更新实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToUpdate"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public async Task<bool> UpdateAsync<T>(T entityToUpdate, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.UpdateAsync<T>(entityToUpdate, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 删除实体数据
    /// </summary>
    /// <param name="entityToDelete"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public bool Delete(TEntity entityToDelete, IDbTransaction traction = null, int? commandTimeout = null)
    {
      return Delete<TEntity>(entityToDelete, traction, commandTimeout);
    }

    /// <summary>
    ///  删除实体数据
    /// </summary>
    /// <param name="entityToDelete"></param>
    /// <returns></returns>
    public bool Delete(TEntity entityToDelete)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Delete<TEntity>(entityToDelete);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 删除实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToDelete"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public bool Delete<T>(T entityToDelete, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return conn.Delete(entityToDelete, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 删除实体数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToDelete"></param>
    /// <param name="traction">此次操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <returns></returns>
    public async Task<bool> DeleteAsync<T>(T entityToDelete, IDbTransaction traction = null, int? commandTimeout = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          return await conn.DeleteAsync<T>(entityToDelete, traction, commandTimeout);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 执行原生sql
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <returns></returns>
    public int Execute(string sql, object param = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);
          sql = DapperCommon.ParameterReplace(conn, sql);
          return conn.Execute(sql, param);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 执行原生sql
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <returns></returns>
    public async Task<int> ExecuteAsync(string sql, object param = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.ExecuteAsync(sql, param);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 执行原生sql
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null,
      CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return conn.Execute(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 执行原生sql
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 查询  
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public IEnumerable<TEntity> Query(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return conn.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 查询
    /// </summary>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<IEnumerable<TEntity>> QueryAsync(string sql, object param = null,
      IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.QueryAsync<TEntity>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 查询并返回指定对象
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 查询并返回指定对象
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 查询并返回第一个对象  
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public T QueryFirst<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return conn.QueryFirst<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 查询并返回第一个对象
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<T> QueryFirstAsync<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.QueryFirstAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// 查询并返回第一个对象
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return conn.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Async 查询并返回第一个对象 
    /// </summary>
    /// <typeparam name="T">对象</typeparam>
    /// <param name="sql">SQL脚本</param>
    /// <param name="param">SQL执行参数对象</param>
    /// <param name="transaction">操作的事务对象</param>
    /// <param name="commandTimeout">执行的超时时间</param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          return await conn.QueryFirstOrDefaultAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public IEnumerable<TEntity> QueryIn(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return conn.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryAsyncIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<IEnumerable<TEntity>> QueryAsyncIn(string sql, object param = null,
      IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return await conn.QueryAsync<TEntity>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="buffered"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public IEnumerable<T> QueryIn<T>(string sql, object param = null, IDbTransaction transaction = null,
      bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryAsyncIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<IEnumerable<T>> QueryAsyncIn<T>(string sql, object param = null,
      IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
      where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return await conn.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryFirstIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public T QueryFirstIn<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return conn.QueryFirst<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryFirstAsyncIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<T> QueryFirstAsyncIn<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null) where T : class, new()
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return await conn.QueryFirstAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryFirstOrDefaultIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public T QueryFirstOrDefaultIn<T>(string sql, object param = null, IDbTransaction transaction = null,
      int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return conn.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// QueryFirstOrDefaultAsyncIn 该函数会对入参匿名对象param进行List类型检索，当包含List参数集合时避免ListCount等于零
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public async Task<T> QueryFirstOrDefaultAsyncIn<T>(string sql, object param = null,
      IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
      try
      {
        using (var conn = _connection)
        {
          OpenConnection(conn);

          sql = DapperCommon.ParameterReplace(conn, sql);

          ListExtensions.ParamIsListEmpty(ref param);
          return await conn.QueryFirstOrDefaultAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
      }
      catch
      {
        throw;
      }
      finally
      {
        Dispose();
      }
    }

    /// <summary>
    /// Connection Close
    /// </summary>
    public void Dispose()
    {
      if (_connection != null)
      {
        if (_connection.State != ConnectionState.Closed)
        {
          _connection.Close();
        }

        _connection.Dispose(); // 确保释放连接对象的资源
      }
    }
    
    /// <summary>
    /// Connection Close
    /// </summary>
    public void Dispose(IDbConnection con)
    {
      if (con != null)
      {
        if (con.State != ConnectionState.Closed)
        {
          con.Close();
        }

        con.Dispose(); // 确保释放连接对象的资源
      }
    }
    
    /// <summary>
    /// Connection Close
    /// </summary>
    public void Close()
    {
      if (_connection != null)
      {
        if (_connection.State != ConnectionState.Closed)
        {
          _connection.Close();
        }

        _connection.Dispose(); // 确保释放连接对象的资源
      }
    }
    
    /// <summary>
    /// Connection Close
    /// </summary>
    public void Close(IDbConnection con)
    {
      if (con != null)
      {
        if (con.State != ConnectionState.Closed)
        {
          con.Close();
        }

        con.Dispose(); // 确保释放连接对象的资源
      }
    }

    ~DbRepository()
    {
      Close(_connection);
      GC.SuppressFinalize(this);
    }
}